#!/usr/bin/python

import getopt, sys, traceback
import socket, os
import logging
import ConfigParser

import MySQLdb
import MySQLdb.cursors

try:
    from sqlalchemy.databases import mysql
    from sqlalchemy import Column, Integer, String, Boolean, DateTime
    from sqlalchemy import create_engine, MetaData
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.sql import and_, or_
    
    from sqlalchemy.orm.exc import NoResultFound
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.exc import OperationalError
except ImportError:
    print "SQLAlchemy needs to be installed."
    sys.exit(1)

from datetime import datetime
from time import sleep

CONFIG_LOOKUP = [os.path.expanduser('~/.mymr.cfg'), "/etc/mymr.cfg"]

logger = logging.getLogger('setauth')
logger.setLevel(logging.INFO)

hdlr = logging.StreamHandler(sys.stdout)
hdlr.setFormatter( logging.Formatter('%(asctime)s %(levelname)s %(message)s') )
logger.addHandler(hdlr)

hdlr = logging.FileHandler('mymrotate.log')
hdlr.setFormatter( logging.Formatter('%(asctime)s %(levelname)s %(message)s') )
logger.addHandler(hdlr)

def parse_configuration():
    """
    Attempt to read a configuration file or create a new one containing some
    default values.
    """

    config = ConfigParser.ConfigParser()
    
    read_configs = config.read(CONFIG_LOOKUP)
    
    if len(read_configs) == 0:
    
        # set a number of parameters
        config.add_section("dist_server")
    
        config.set("dist_server", "host", socket.gethostname())
        config.set("dist_server", "port", str(3306))
    
        config.set("dist_server", "username", "root")
        config.set("dist_server", "password", "")
    
        config.set("dist_server", "database", "mysql")

        # set a number of parameters
        config.add_section("replication")

        config.set("replication", "username", "replicator")
        config.set("replication", "password", "repl-password")
        config.set("replication", "only_databases", "")

        logger.info("writing configuration to %s" % (CONFIG_LOOKUP[-1]))
    
        fp = open(CONFIG_LOOKUP[0], "wb")
        config.write(fp)
        fp.close()
    else:
        logger.debug("read configuration from %s" % (read_configs))

    return config

config = parse_configuration()

engine = create_engine('mysql://%s:%s@%s:%d/%s' % (
    config.get("dist_server", "username"),
    config.get("dist_server", "password"),
    config.get("dist_server", "host"),
    config.getint("dist_server", "port"),
    config.get("dist_server", "database"),
), echo=False)

DeclarativeBase = declarative_base()
metadata = MetaData(engine)
Session = sessionmaker(bind=engine)
DBSession = Session()

class Server(DeclarativeBase):
    """
    This class provides some basic methods to interact with a MySQL server.

    It is also inherited by DistributionServer which provides some additional
    methods.
    """

    __tablename__ = 'mymr'

    id = Column(Integer, primary_key=True)

    server_id = Column(Integer)
    master_host = Column(String(64))
    master_port = Column(Integer)
    master_log_file = Column(String(64))
    master_log_pos = Column(mysql.MSInteger(unsigned=True))
    dist_server = Column(Boolean())

    admin_user = Column(String(64))
    admin_passwd = Column(String(64))

    replication_user = Column(String(64))
    replication_passwd = Column(String(64))

    auto_increment_offset = Column(mysql.MSInteger(unsigned=True))
    auto_increment_increment = Column(mysql.MSInteger(unsigned=True))

    last_error = Column(String(64))

    last_active = Column(DateTime)
    last_success = Column(DateTime)

    engine = None
    _session = None

    def __init__(self, uri = None):
    
        if uri:
            uri = "mysql://" + uri
            engine = create_engine(uri)
            self.master_host = engine.url.host
            self.master_port = engine.url.port
            self.admin_user = engine.url.username
            self.admin_passwd = engine.url.password

            if not self.admin_user:
                try:
                    self.autodiscover_credentials()
                except Exception, e:
                    logging.error("could not guess username and password")
                    logging.error("please specify it in the form of username:password@hostname:port")
                    logging.error(e)
                    sys.exit(-1)

        self.set_session(as_admin = True)

    def __repr__(self):
        if self.master_port != 3306:
            return "%s:%s" % (self.master_host, self.master_port)
        else:
            return self.master_host

    def __eq__(self, other):
        return other and self.master_host == other.master_host and self.master_port == other.master_port

    def __ne__(self, other):
        return not self.__eq__(other)

    def new_session(self, as_admin = False, database = ""):
        """
        Generate and return a Session object
        """
        host = self.master_host
        port = self.master_port or 3306

        if as_admin:
            username = self.admin_user
            password = self.admin_passwd
        else:
            username = self.replication_user
            password = self.replication_passwd

        engine = create_engine('mysql://%s:%s@%s:%d/%s' % (
            username,
            password,
            host,
            port,
            database
        ), echo=False)
        
        Session = sessionmaker(bind=engine)

        return Session()

    def set_session(self, as_admin):
        """
        Create and store a Session for this database
        """
        self._session = self.new_session(as_admin = as_admin)

    @property
    def session(self):
        """
        Lazy session property, create one if not done yet
        """
        if not self._session:
            self.set_session(as_admin = True)
        return self._session

    def autodiscover_credentials(self):
        """
        Attempt to autodiscover the credentials by using the same ones
        used on other servers.
        """

        logger.info("attempting to autodiscover credentials for %s..." % (self))

        for server in master_server.servers:

            logger.debug("trying %s/%s" % (
                server.admin_user,
                server.admin_passwd
            ))

            try:
                engine = create_engine('mysql://%s:%s@%s:%d/%s' % (
                    server.admin_user,
                    server.admin_passwd,
                    self.master_host,
                    self.master_port,
                    ""
                ), echo=False)
                sessionmaker(bind=engine)().execute("SHOW MASTER STATUS;")
            except OperationalError:
                pass
            else:
                logger.info("found credentials!")
                self.admin_user = server.admin_user
                self.admin_passwd = server.admin_passwd
                break

    def get(self, field, default = None):
        """
        Wrapper for getattr() which allows returning a default value in case
        of KeyError
        """
        try:
            return getattr(self, field)
        except KeyError:
            return default

    def autoconfigure_reverse_replication(self):
        """
        Setup reverse replication from server to master_server
        """

        ds_file, ds_pos = master_server.master_file_and_pos()

        self.query("""
            CHANGE MASTER TO MASTER_HOST='%s',
            MASTER_PORT=%d,
            MASTER_USER='%s',
            MASTER_PASSWORD='%s',
            MASTER_LOG_FILE='%s',
            MASTER_LOG_POS=%d;
        """ % (
            master_server.master_host,
            master_server.master_port,
            master_server.replication_user,
            master_server.replication_passwd,
            ds_file,
            ds_pos,
        ))
        self.query("""START SLAVE;""")

    def get_server_variable(self, varname):
        """
        Query server for a variable and return its value
        """
        row = self.fetchone("""SHOW GLOBAL VARIABLES LIKE '%s';""" % (varname))
        if row and row["Variable_name"] == varname:
            return row["Value"]
        return None

    def master_file_and_pos(self):
        """
        Query and return replication master status (file and position)
        """
        master_status = self.fetchone("SHOW MASTER STATUS;")
        if master_status:
            return master_status["File"], int(master_status["Position"])
        else:
            return None, None

    def has_logbin(self):
        """
        Shortcut for knowing whether log_bin is enabled on the server
        """
        return self.get_server_variable("log_bin") == "ON"

    def get_auto_increment(self):
        """
        Return value of auto_increment_offset and auto_increment_increment
        """
        toret = {
            "auto_increment_offset" : None,
            "auto_increment_increment" : None
        }
        for row in self.fetchall(
            """SHOW GLOBAL VARIABLES LIKE 'auto_increment_%';"""
        ):
            if row["Variable_name"] in [
                "auto_increment_offset",
                "auto_increment_increment"
            ]:
                toret[row["Variable_name"]] = int(row["Value"])
        return toret["auto_increment_offset"], toret["auto_increment_increment"]

    def fix_autoincrements(self):
        """
        Iterate over each table and change AUTO_INCREMENT in order to avoid
        conflicts with other servers.
        """

        for database in master_server.active_on_databases():

            session = self.new_session(as_admin = True, database = database)

            for row in session.execute("SHOW TABLES").fetchall():

                table = row["Tables_in_%s" % (database)]

                for row2 in session.execute('SHOW TABLE STATUS LIKE "%s";' % (
                    table
                )).fetchall():

                    # Lookup the autoincrement field
                    for row3 in session.execute("DESCRIBE %s" % (table)).fetchall():
                        if row3["Extra"].find("auto_increment") >= 0:
                            auto_increment_field = row3["Field"]
                            break
                    else:
                        if row2["Auto_increment"]:
                            logger.error(
                            "could not identify autoincrement field for %s.%s!"
                                % ( database, table ))
                            sys.exit(1)

                        logger.debug("%s.%s does has no autoincrement field" % (
                            database, table
                        ))
                        continue

                    max_id = master_server.fetchone(
                        'SELECT MAX(%s) as max_id FROM %s.%s LIMIT 1;' % (
                            auto_increment_field, database, table
                        )
                    )["max_id"]

                    if max_id == None:
                        logger.debug("DS server is empty, no need to change next autoincrement for %s" % (table))
                        continue

                    logger.debug("table %s (id %s) at %s (ds is at %s)" % (
                        table,
                        auto_increment_field,
                        row2["Auto_increment"],
                        max_id
                    ))
                    
                    if row2["Auto_increment"] < max_id:
                        logger.warning("risk of collision, increasing AUTO_INCREMENT for %s.%s to %d" % (database,table,max_id))
                        self.query("""ALTER TABLE %s AUTO_INCREMENT = %d;""" % (table, max_id))

    def verify_master_configuration(self):
        """
        Perform sanity checks over the server's configuration.
        """

        server = self

        logger.info("checking configuration on server %s..." % (self))
        
        self.set_session(as_admin = True)

        errors = 0

        server_id = server._server_id()
        if master_server.fetchone("SELECT master_host, master_port FROM mymr WHERE server_id=%d" % (server_id)):
            logger.error("server_id %d is already in use" % (server_id))
            errors += 1

        if not server.has_logbin():
            logger.error("master not running on this host")
            errors += 1

        slave_status = server.fetchone("SHOW SLAVE STATUS;")
        if (slave_status
           and slave_status["Master_Host"] != master_server.master_host
           and slave_status["Master_Host"] == "127.0.0.1"):
            logger.error("a previous slave configuration exists pointing to a different server")
            errors += 1

        # Should not log slave updates
        if self.get_server_variable("log_slave_updates") == "ON":
            logger.error("should not log slave updates")
            errors += 1

        if self.get_server_variable("max_allowed_packet") != master_server.get_server_variable("max_allowed_packet"):
            logger.error("max_allowed_packet is different than the distribution server")
            errors += 1

        if len(master_server.list_binlog_databases() - server.list_databases()) > 0:
            logger.error("at least one database is missing on the new server")
            errors += 1

        # TODO: replicate-same-server-id correctly set

        offset, increment = server.get_auto_increment()

        if master_server.fetchone("SELECT master_host, master_port FROM mymr WHERE auto_increment_offset=%d" % (offset)):
            logger.error("auto_increment_offset (%d) is already being used by another server" % (offset))
            errors += 1
        if increment != 10:
            logger.error("recommended value for auto_increment_increment is 10 (currently is %d)" % (increment))
            errors += 1

        return errors

    def troubleshoot(self):
        """
        Perform sanity checks over the server's configuration which is already
        part of the replication network.
        """

        server = self

        logger.info("troubleshooting server %s..." % (self))

        warn = 0
        errors = 0

        try:
            grants = server.fetchone("""SELECT 1;""")
        except OperationalError:
            logger.error("cannot execute a query")
            logger.error("please check the server connection details")
            errors += 1
            return errors            

        try:
            grants = server.fetchone("""SHOW GRANTS FOR '%s'@'%%';""" % (
                server.replication_user
            ))
        except OperationalError:
            try:
                grants = server.fetchone("""SHOW GRANTS FOR '%s'@'%s';""" % (
                    server.replication_user, server.master_host
                ))
            except OperationalError:
                logger.error("no user matching %s@%% or %s@%s" % (
                    server.replication_user,
                    server.replication_user,
                    server.master_host,
                ))
                errors += 1
        
        return errors

    def refresh_master_position(self):
        """
        Retrieve and store master log file and position
        """
        self.master_log_file, self.master_log_pos = self.master_file_and_pos()

    def refresh_server_id(self):
        """
        Retrieve and store server_id (which must be unique)
        """
        self.server_id = self._server_id()
        if master_server.fetchone("SELECT master_host, master_port FROM mymr WHERE server_id=%d" % (self.server_id)):
            raise Exception("Duplicate server_id")

    def _server_id(self):
        """
        Query and return server_id
        """
        return int(self.get_server_variable("server_id"))

    def list_databases(self):
        """
        Returns the list of databases on the server
        """
        toret = []
        for row in self.fetchall("""SHOW DATABASES;"""):
            toret.append(row[0])
        return set(toret)

    def list_binlog_databases(self):
        """
        Returns the list of databases on the server for which we have a binlog
        """

        master_status = self.fetchone("SHOW MASTER STATUS;")

        if master_status["Binlog_Do_DB"]:
            return master_status["Binlog_Do_DB"].split(",")

        dbs = self.list_databases()

        return dbs - set(master_status["Binlog_Ignore_DB"].split(","))

    def create_replication_user(self, user, passwd):
        """
        Create a user suitable to use as a replication client
        """

        session = self.new_session(as_admin = True)
        session.execute("""GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO %s@'%s' IDENTIFIED BY '%s';""" % (
            user,
            '%',
            passwd
        ))
        session.execute("""FLUSH PRIVILEGES;""")
        self.replication_user = user
        self.replication_passwd = passwd

    def query(self, sql, session = None):
        """
        Execute a raw SQL query
        """
        logger.debug("executing sql on %s: %s" % (self, sql))
        if not session:
            session = self.session
        try:
            return session.execute(sql)
        except Exception, e:
            logger.error("error in sql: %s" % (sql))
            raise e

    def fetchone(self, sql, session = None):
        """
        Execute a raw SQL query and return one result
        """
        if not session:
            session = self.session
        return self.query(sql).fetchone()

    def fetchall(self, sql, session = None):
        """
        Execute a raw SQL query and return all results
        """
        if not session:
            session = self.session
        return self.query(sql).fetchall()

class DistributionServer(Server):
    """
    DistributionServer provides some methods to lookup (or create) Server
    instances.
    
    Aside from inheriting Server, it implements an algorithm to elect the next
    server to which rotate and a function to print the current status in text
    format.

    Additionally it performs sanity checks which are required for the central
    node (DS).
    """

    def lookup_server(self, **kwargs):
        """
        Get and return a server
        """
        server = self.servers.filter_by(**kwargs).one()
        if server == self:
            return self
        return server

    def new_session(self, as_admin = True):
        """
        Create a database session (use currently or create a new one if
        necessary)
        """
        if as_admin:
            return DBSession
        else:
            return Server.new_session(self, as_admin = False)

    def verify_distserver_configuration(self):
        """
        Perform a sanity check for the Distribution server configuration
        """

        ds = self

        logger.info("checking configuration on distribution server %s..." % (self))

        errors = 0

        server_id = ds._server_id()
        if ds.fetchone("SELECT master_host, master_port FROM mymr WHERE server_id=%d AND master_host!='%s' and master_port!='%d';" % (server_id, ds.master_host, ds.master_port)):
            logger.error("server_id %d is already in use" % (server_id))
            errors += 1

        if not ds.has_logbin():
            logger.error("master not running on this host")
            errors += 1

        # Should not log slave updates
        if ds.get_server_variable("log_slave_updates") == "OFF":
            logger.error("should be logging slave updates")
            errors += 1

        if len(ds.list_binlog_databases()) == 0:
            logger.error("binlog needs to be enabled for at least one database")
            errors += 1

        offset, increment = ds.get_auto_increment()

        if ds.fetchone("SELECT master_host, master_port FROM mymr WHERE auto_increment_offset=%d" % (offset)):
            logger.error("auto_increment_offset (%d) is already being used by another server" % (offset))
            errors += 1
        if increment != 10:
            logger.error("recommended value for auto_increment_increment is 10 (currently is %d)" % (increment))
            errors += 1

        return errors > 0

    def troubleshoot(self):

        ds = self
        errors = 0

        logger.info("troubleshooting DS %s..." % (ds))

        return errors

    @property
    def active_server(self):
        """
        Return the active Server object
        """

        slave_status = self.fetchone("SHOW SLAVE STATUS;")
        return master_server.lookup_server(
            master_host = slave_status["Master_Host"],
            master_port = slave_status["Master_Port"]
        )

    @property
    def servers(self):
        """
        Return all configured Server objects
        """
        return DBSession.query(Server).order_by("server_id")

    @property
    def working_servers(self):
        """
        Return all Server objects which reported no error
        """
        return self.servers.filter(
            and_(Server.dist_server == False,
            or_(
                Server.last_error == None,
                Server.last_error == ""
            )
        ))

    def position_diff_score(self, server):
        """
        Calculate the difference between a Server and the DS
        """

        fname, pos = server.master_file_and_pos()
        score_1 = ( int(fname.rsplit(".", 1)[-1]) - 
                  int(server.master_log_file.rsplit(".", 1)[-1]))

        if score_1 == 0:
            return 0, pos - server.master_log_pos
        else:
            return score_1, pos

    def next_server(self, active_server):
        """
        Elect who the next server should be based on which server has got the most changes to propagate
        """

        elected = (None, None, None)

        server = None

        for server in self.working_servers.order_by(Server.last_active):
            try:
                if active_server and server == active_server:
                    continue

                logfile, position = server.master_file_and_pos()

                score_1, score_2 = self.position_diff_score(server)
                score = score_1 * 1000000 + score_2

                if ((score_1 > 0 or score_2 > 0) and
                   (not elected or elected[0] < score_1 or elected[1] < score_1)):
                    elected = (score_1, score_2, server)

                logger.debug(
                    "evaluating server %s was at position %s:%s now at %s:%s (score %d.%d)" % (
                        server,
                        server.master_log_file, server.master_log_pos,
                        logfile, position,
                        score_1, score_2
                ))
            except Exception, e:
                logger.exception("could not get current status for server (%s)" % (e))
                traceback.print_exc()

        if not active_server and not elected[1]:
            # If there's no active server and no elected, just pick one to get replication started...
            return server

        return elected[1]

    def print_status(self):
        """
        Print to screen the current replication status
        """

        master_server = self
        master_slave_status = master_server.fetchone("SHOW SLAVE STATUS;")

        try:
            active_master = master_server.lookup_server(
                master_host = master_slave_status["Master_Host"],
                master_port = master_slave_status["Master_Port"]
            )
        except NoResultFound, e:
            active_master = None

        print "\n%2s | %20s | %10s" % (
            "ID",
            "Host",
            "Status",
        )
        print "+--+----------------------+------------+"

        for server in master_server.servers:

            errors = []

            if server == master_server:
                score = "(DS)"
            else:
                try:    
                    if server == active_master:
        
                        if (not master_slave_status 
                            or master_slave_status["Slave_IO_Running"] != "Yes" 
                            or master_slave_status["Slave_SQL_Running"] != "Yes"
                        ):
                            score = "-> error"
                        else:
                            score = "-> active"
    
                    else:
                        score_1, score_2 = self.position_diff_score(server)
                        score = "%d.%d" % (score_1, score_2)
                except Exception, e:
                    # That's ok, we just couldn't fetch the current master file/position
                    logger.exception("exception caught for %s (%s)" % (server, e))
                    score = "???"
    
                try:
                    slave_status = server.fetchone("SHOW SLAVE STATUS;")
                    if (not slave_status 
                        or slave_status["Master_Host"] != master_server.master_host
                        or slave_status["Slave_IO_Running"] != "Yes"
                        or slave_status["Slave_SQL_Running"] != "Yes"):
                        errors.append("oneway")
        
                    if not server.last_error in ["", None]:
                        errors.append("slave_error")
                except Exception, e:
                    logger.exception("exception caught for %s (%s)" % (server, e))

            print " %2d| %20s | %10s | %s" % (
                server.server_id,
                server,
                score,
                ",".join(errors)
            )

        if master_slave_status["Last_Errno"] != 0 or master_slave_status["Last_Error"]:
            print
            print "Error %d: %s" % (
                master_slave_status["Last_Errno"],
                master_slave_status["Last_Error"]
            )

        print

    def active_on_databases(self):
        """
        Return a list of databases which are being replicated
        """
        return config.get(
            "replication", "only_databases",
            master_server.list_binlog_databases()
        )

try:
    DeclarativeBase.metadata.create_all(engine) 
except OperationalError, e:
    logger.error("could not create tables! check your database settings")
    logger.error(e)
    sys.exit(1)

try:
    master_server = DBSession.query(DistributionServer).filter_by(
        master_host = config.get("dist_server", "host"),
        master_port = config.getint("dist_server", "port"),
        admin_user = config.get("dist_server", "username"),
        admin_passwd = config.get("dist_server", "password"),
        replication_user = config.get("replication", "username"),
        replication_passwd = config.get("replication", "password"),
        dist_server=True
    ).order_by("server_id").one()

except NoResultFound, e:

    logging.debug("no matching DS server was found in the database")

    add_to_db = False

    try:
        master_server = (DBSession.query(DistributionServer)
                        .filter_by(dist_server=True)
                        .order_by("server_id").one())
    except NoResultFound, e:
        logging.info("creating DS entry in the database")
        master_server = DistributionServer()
        add_to_db = True
    else:
        logging.warn("DS details have changed - updating database")

    master_server.master_host = config.get("dist_server", "host")
    master_server.master_port = config.getint("dist_server", "port")
    master_server.admin_user = config.get("dist_server", "username")
    master_server.admin_passwd = config.get("dist_server", "password")
    master_server.replication_user = config.get("replication", "username")
    master_server.replication_passwd = config.get("replication", "password")
    master_server.server_id = master_server._server_id()
    master_server.dist_server = True

    if master_server.verify_distserver_configuration() > 0:
        logger.error("Please check the errors above and try again")
        sys.exit(1)

    logger.info("DS configuration check PASSED")

    if add_to_db:
        DBSession.add(master_server)

next_master = None

try:
    opts, args = getopt.getopt(sys.argv[1:], "ha:t:s:vl", ["help", "add="])
except getopt.GetoptError, err:
    # print help information and exit:
    print str(err)
    print """
    Please see http://code.google.com/p/mymr/ for intructions.
    """
    sys.exit(2)

for o, a in opts:
    if o == "-v":
        logger.setLevel(logging.DEBUG)
    elif o in ("-h", "--help"):
        print "usage()"
        sys.exit()
    elif o in ("-l", "--list"):
        master_server.print_status()
        sys.exit()
    elif o in ("-t", "--troubleshoot"):
        errors = master_server.lookup_server(server_id = int(a)).troubleshoot()
        if errors == 0:
            logger.info("no errors detected")
        else:
            logger.warn("%d errors detected" % (errors))
        sys.exit()
    elif o in ("-s", "--set"):
        next_master = master_server.servers.filter_by(server_id = int(a)).one()
    elif o in ("-a", "--add"):

        server = Server(a)

        try:
            master_server.lookup_server(master_host = server.master_host, master_port = server.master_port)
        except NoResultFound, e:
            pass
        else:
            logger.error("server %s is already present in the masters list" % (server))
            sys.exit(1)
        
        if server.verify_master_configuration() > 0:
            logger.error("please check the errors above and try again")
            sys.exit(1)

        server.fix_autoincrements()

        server.create_replication_user(
            config.get("replication", "username"),
            config.get("replication", "password"
        ))

        server.refresh_master_position()
        server.refresh_server_id()

        DBSession.add(server)

        slave_status = server.fetchone("SHOW SLAVE STATUS;")

        if (slave_status and slave_status["Slave_SQL_Running"] == "Yes" 
           and slave_status["Slave_IO_Running"] == "Yes"):
            logging.warning("an existing slave configuration is active on %s - skipping autoconfiguration" % (server))
        else:
            server.autoconfigure_reverse_replication()

        del server

    else:
        assert False, "unhandled option"

slave_status = master_server.fetchone("SHOW SLAVE STATUS;")
active_master = None

if slave_status["Master_Host"]:

    # Lookup (or add) the active master

    try:
        active_master = master_server.lookup_server(
            master_host = slave_status["Master_Host"],
            master_port = slave_status["Master_Port"]
        )
    except NoResultFound, e:

        logger.info("detected unknown master in configuration (host %s:%d at %s:%d)" % (
            slave_status["Master_Host"],
            slave_status["Master_Port"],
            slave_status["Master_Log_File"],
            slave_status["Read_Master_Log_Pos"],
        ))

        logger.info("try adding it: mymrotate.py -a %s:%d" % (
            slave_status["Master_Host"],
            slave_status["Master_Port"]
        ))
        sys.exit()

    else:
        if slave_status["Last_Error"]:
            logger.warn("some errors were detected: %s" % (slave_status["Last_Error"]))

        id_offset = active_master.id
else:
    # No replication configured
    active_master = None

if not next_master:
    next_master = master_server.next_server(active_master)

if next_master and (not active_master or next_master != active_master):

    if (slave_status and (slave_status["Slave_IO_Running"] == "Yes" 
        or slave_status["Slave_SQL_Running"] == "Yes")):
        master_server.query("SLAVE STOP;")
        slave_status = master_server.fetchone("SHOW SLAVE STATUS;")

        if active_master:

            # We knew this server, update its info
        
            active_master.master_log_file = slave_status["Master_Log_File"]
            active_master.master_log_pos = slave_status["Read_Master_Log_Pos"]
            active_master.last_error = slave_status["Last_Error"]
            active_master.last_active = datetime.now()
            
            if slave_status["Slave_SQL_Running"] == "Yes":
                active_master.last_success = datetime.now()

            DBSession.add(active_master)

    master_server.query("""
        CHANGE MASTER TO MASTER_HOST='%s',
        MASTER_PORT=%d,
        MASTER_USER='%s',
        MASTER_PASSWORD='%s',
        MASTER_LOG_FILE='%s',
        MASTER_LOG_POS=%d;
    """ % (
        next_master.master_host,
        next_master.master_port,
        next_master.replication_user,
        next_master.replication_passwd,
        next_master.master_log_file,
        next_master.master_log_pos
    ))

    active_master = next_master

    master_server.query("""SLAVE START;""")

    logger.info("changed to new master: %s" % (next_master))

    retries = 3
    while retries > 0:

        # Give some time for replication to begin...
        sleep(0.5)

        slave_status = master_server.fetchone("SHOW SLAVE STATUS;")

        if not slave_status:
            logger.error("no replication configured!?")
            sys.exit(1)

        if slave_status["Slave_IO_Running"] == "Yes":
            if slave_status["Slave_SQL_Running"] == "Yes":
                logger.info("replication was successfully started")
                break
            else:
                logger.error("slave connected but not propagating updates (slave IO state: %s)" % (slave_status['Slave_IO_State']))
                break

        logger.warning("replication has not started yet (retries %d)" % (retries))
        retries -= 1
else:
    logger.info("no change needed")
    if (not slave_status or slave_status["Slave_SQL_Running"] != "Yes" or 
        slave_status["Slave_IO_Running"] != "Yes"):
        logger.warning("replication is *not* working - please check")

master_server.print_status()